package com.exercise.baseskill.excel;

import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.Sheet;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Description  easyExcel 读取excel内存压力测试
 * 10W 数据 POI excel2007 需要300M 容量才能完成解析
 * 10W 数据 easyExcel 只需要50M 就能玩成解析
 * @Date 2021/6/24 13:47
 * @Created by zhouchungui
 */
public class EasyExcelRead {
    //简单读取
    public static void simpleRead(String readPath){
        // 读取 excel 表格的路径

        try {
            // sheetNo --> 读取哪一个 表单
            // headLineMun --> 从哪一行开始读取( 不包括定义的这一行，比如 headLineMun为2 ，那么取出来的数据是从 第三行的数据开始读取 )
            // clazz --> 将读取的数据，转化成对应的实体，需要 extends BaseRowModel
            Sheet sheet = new Sheet(1, 1, ExcelMode.class);

            // 这里 取出来的是 ExcelModel实体 的集合
            List<Object> readList = EasyExcelFactory.read(new FileInputStream(readPath), sheet);
            // 存 ExcelMode 实体的 集合
            List<ExcelMode> list = new ArrayList<ExcelMode>();
            for (Object obj : readList) {
                list.add((ExcelMode) obj);
            }

            // 取出数据
            StringBuilder str = new StringBuilder();
            str.append("{");
            String link = "";
            for (ExcelMode mode : list) {
                str.append(link).append("\""+mode.getCardNo()+"\"");
                link= ",";
            }
            str.append("};");
            System.out.println(str);

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static void simpleRead1(String readPath){
        try {
            Sheet sheet = new Sheet(1,1,ExcelMode.class);
            EasyExcelFactory.readBySax(new FileInputStream(readPath),sheet,new ExcelModelListener());

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        String readPath = "C:\\Users\\zhouchungui\\Desktop\\汇总10W.xls";
        System.out.println("解析Excel前内存:"+(Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory())/(1024)+"KB");
//        EasyExcelRead.simpleRead(readPath);  //40M 的内存即可完成解析
        EasyExcelRead.simpleRead1(readPath);  // 40M 的内存即可完成解析 对内存要求比同步更低
        System.out.println("解析Excel后内存:"+(Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory())/(1024)+"KB");




    }

    static class ExcelModelListener extends AnalysisEventListener<ExcelMode>{

        /**
         * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
         */
        private static final int BATCH_COUNT = 5;
        List<ExcelMode> list = new ArrayList<ExcelMode>();
        private static int count = 1;

        @Override
        public void invoke(ExcelMode data, AnalysisContext context) {
//            System.out.println("解析到一条数据:{ "+ data.toString() +" }");
            list.add(data);
            count ++;
            if (list.size() >= BATCH_COUNT) {
                saveData( count );
                list.clear();
            }
        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
            saveData( count );
            System.out.println("所有数据解析完成！");
            System.out.println(" count ：" + count);
        }

        /**
         * 加上存储数据库
         */
        private void saveData(int count) {
            System.out.println("{ "+ count +" }条数据，开始存储数据库！" + list.size());
            System.out.println("存储数据库成功！");
        }
    }
}
